using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace DAL
{
    public class tbdonhangDAL
    {
        private SqlConnection sqlCn = null;
        static public string error = "";
        public tbdonhangDAL()
        {
            string stringConnection = ConfigurationSettings.AppSettings["connectionstring"].ToString();
            sqlCn = new SqlConnection(stringConnection);
            sqlCn.Open();
        }
        ~tbdonhangDAL()
        {
            //this.sqlCn.Close();
            //this.sqlCn.Dispose();
        }

        public int Insert(tbdonhangDTO dh)
        {
            SqlCommand cm = new SqlCommand("sp_donhang_Insert", this.sqlCn);
            cm.CommandType = CommandType.StoredProcedure;
            cm.Parameters.AddWithValue("@makh", dh.Makh);
            cm.Parameters.AddWithValue("@ngaydathang", dh.Ngaydathang);
            cm.Parameters.AddWithValue("@ngaygiaohang", dh.Ngaygiaohang);
            cm.Parameters.AddWithValue("@tinhtrang", dh.Tinhtrang);
            cm.Parameters.AddWithValue("@trigia", dh.Trigia);
            cm.Parameters.AddWithValue("@diachinhanhang", dh.Diachinhanhang);
            cm.Parameters.AddWithValue("@manv", dh.Manv);
            cm.Parameters.AddWithValue("@matinhtrang", dh.Matinhtrang);
           
            try
            {
                cm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                error = ex.Message;
            }
            string cautruyvan = "select top 1 madh from donhang order by madh desc";
            cm = new SqlCommand(cautruyvan, this.sqlCn);
            SqlDataReader dr = cm.ExecuteReader(CommandBehavior.SingleRow);
            dr.Read();
            return int.Parse(dr["madh"].ToString());
        }

        public void Update(tbdonhangDTO dh)
        {
            SqlCommand cm = new SqlCommand("sp_donhang_Update", this.sqlCn);
            cm.CommandType = CommandType.StoredProcedure;
            cm.Parameters.AddWithValue("@madh", dh.Madh);
            cm.Parameters.AddWithValue("@makh", dh.Makh);
            cm.Parameters.AddWithValue("@ngaydathang", dh.Ngaydathang);
            cm.Parameters.AddWithValue("@ngaygiaohang", dh.Ngaygiaohang);
            cm.Parameters.AddWithValue("@tinhtrang", dh.Tinhtrang);
            cm.Parameters.AddWithValue("@trigia", dh.Trigia);
            cm.Parameters.AddWithValue("@diachinhanhang", dh.Diachinhanhang);
            cm.Parameters.AddWithValue("@manv", dh.Manv);
            try
            {
                cm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                error = ex.Message;
                return;
            }
        }

        public void Delete(int Ma)
        {
            SqlCommand cm = new SqlCommand("sp_donhang_Delete", this.sqlCn);
            cm.CommandType = CommandType.StoredProcedure;

            cm.Parameters.AddWithValue("@madh", Ma);

            cm.ExecuteNonQuery();
        }

        public tbdonhangDTOColection GET(string Where, int PageIndex, int PageSize, string OrderBy,
                                       string OrderDirection, out int TotalRecords)
        {
            SqlCommand cm = new SqlCommand("sp_donhang_GET", this.sqlCn);
            cm.CommandType = CommandType.StoredProcedure;


            cm.Parameters.AddWithValue("@Where", Where);
            cm.Parameters.AddWithValue("@PageIndex", PageIndex);
            cm.Parameters.AddWithValue("@PageSize", PageSize);
            cm.Parameters.AddWithValue("@OrderBy", OrderBy);
            cm.Parameters.AddWithValue("@OrderDirection", OrderDirection);

            SqlParameter param = new SqlParameter("@TotalRecords", DbType.Int32);
            param.Direction = ParameterDirection.Output;
            cm.Parameters.Add(param);


            SqlDataAdapter da = new SqlDataAdapter(cm);
            DataSet ds = new DataSet();
            da.Fill(ds);

            tbdonhangDTOColection tbdonhangDTOColection = new tbdonhangDTOColection();
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                tbdonhangDTO dh = new tbdonhangDTO();
                dh.Madh= int.Parse(ds.Tables[0].Rows[i]["madh"].ToString());
                dh.Makh = int.Parse(ds.Tables[0].Rows[i]["makh"].ToString());
                dh.Ngaydathang = DateTime.Parse(ds.Tables[0].Rows[i]["ngaydathang"].ToString());
                dh.Ngaygiaohang = DateTime.Parse(ds.Tables[0].Rows[i]["ngaygiaohang"].ToString());
                dh.Tinhtrang = int.Parse(ds.Tables[0].Rows[i]["tinhtrang"].ToString());
                dh.Trigia = float.Parse(ds.Tables[0].Rows[i]["trigia"].ToString());
                dh.Diachinhanhang = ds.Tables[0].Rows[i]["diachinhanhang"].ToString();
                dh.Manv = int.Parse(ds.Tables[0].Rows[i]["manv"].ToString());
                tbdonhangDTOColection.Add(dh);
            }
            TotalRecords = int.Parse(cm.Parameters["@TotalRecords"].Value.ToString());

            return tbdonhangDTOColection;
        }
    }
}
